{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4b8c2829",
   "metadata": {},
   "source": [
    "# 生成云账单\n",
    "##### 步骤\n",
    "1. 原始数据\n",
    "2. online、offline计算\n",
    "3. 分产品成本"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "bd8f7939",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dt,service_type,service_name,Cost\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# # D:\\develop\\var\\data\\cloudcost\\cloudcost-202209.csv\n",
    "# path=r'D:\\develop\\var\\data\\cloudcost\\cloudcost-202209-cost.csv'\n",
    "# with open(path,'r') as fr:\n",
    "#     for line in fr.readlines():\n",
    "#         print(line)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "13678751",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "22/10/18 15:40:59 WARN Utils: Your hostname, Dobbins-MBP.local resolves to a loopback address: 127.0.0.1; using 192.168.12.188 instead (on interface en0)\n",
      "22/10/18 15:40:59 WARN Utils: Set SPARK_LOCAL_IP if you need to bind to another address\n",
      "Using Spark's default log4j profile: org/apache/spark/log4j-defaults.properties\n",
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n",
      "22/10/18 15:41:00 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable\n"
     ]
    }
   ],
   "source": [
    "# 如果不存在，执行以下命令安装：pip install pyspark\n",
    "from pyspark.sql import SparkSession\n",
    "spark = SparkSession\\\n",
    "        .builder\\\n",
    "        .appName(\"Demo\")\\\n",
    "        .getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "26b276c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# win\n",
    "# cost_path=r'D:\\develop\\var\\data\\cloudcost\\cloudcost-202209-cost.csv'\n",
    "# online_path=r'D:\\develop\\var\\data\\cloudcost\\cloudcost-202209-online.csv'\n",
    "# pu_path=r'D:\\develop\\var\\data\\cloudcost\\cloudcost-202209-pu.csv'\n",
    "# mac\n",
    "cost_path='/Users/msxr/develop/var/cloudcost/cloudcost-202209-cost.csv'\n",
    "online_path='/Users/msxr/develop/var/cloudcost/cloudcost-202209-online.csv'\n",
    "online2_path='/Users/msxr/develop/var/cloudcost/cloudcost-202209-online2.csv' # 算法比例\n",
    "pu_path='/Users/msxr/develop/var/cloudcost/cloudcost-202209-pu.csv'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c47a90f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "cost_df=spark.read.option(\"header\", True).csv(cost_path)\n",
    "online_df=spark.read.option(\"header\", True).csv(online_path)\n",
    "online2_df=spark.read.option(\"header\", True).csv(online2_path)\n",
    "pu_df=spark.read.option(\"header\", True).csv(pu_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "851be3ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "cost_df.createOrReplaceTempView('t_cost')\n",
    "online_df.createOrReplaceTempView('t_online')\n",
    "online2_df.createOrReplaceTempView('t_online2')\n",
    "pu_df.createOrReplaceTempView('t_pu')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ee0f303f",
   "metadata": {},
   "outputs": [],
   "source": [
    "cost_online_df=spark.sql(\"\"\"\n",
    "select\n",
    "t1.dt,t1.service_type,t1.service_name\n",
    ",Cost*online as online\n",
    ",Cost*offline as offline\n",
    ",Cost*ca as ca\n",
    ",Cost*adx as adx\n",
    ",Cost*sdk as sdk\n",
    ",Cost*san_aff as san_aff\n",
    ",Cost*san_pub as san_pub\n",
    "from (\n",
    "select dt,service_type,service_name,Cost from t_cost\n",
    ")t1\n",
    "left join\n",
    "(\n",
    "select\n",
    "dt,service_type,service_name,  online+offline online,0 offline, ca,adx,sdk,san_aff, san_pub\n",
    "from t_online\n",
    "union all \n",
    "select\n",
    "dt,service_type,service_name,  online+offline online,0 offline, ca,adx,sdk,san_aff, san_pub\n",
    "from t_online2\n",
    ")t2\n",
    " on t1.dt=t2.dt\n",
    "    and t1.service_type=t2.service_type\n",
    "    and t1.service_name=t2.service_name\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "66bce9bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "cost_online_df.createOrReplaceTempView('t_cost_online')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a4cc2655",
   "metadata": {},
   "outputs": [],
   "source": [
    "cost_online_pu_df=spark.sql(\"\"\"\n",
    "select\n",
    "t1.dt,pu_type,pu\n",
    ",t1.online*t2.online as online\n",
    ",t1.offline*t2.offline as offline\n",
    ",t1.ca*t2.ca as ca\n",
    ",t1.adx*t2.adx as adx\n",
    ",t1.sdk*t2.sdk as sdk\n",
    ",t1.san_aff*t2.san_aff as san_aff\n",
    ",t1.san_pub*t2.san_pub as san_pub\n",
    "from (\n",
    "select\n",
    "dt\n",
    ",sum(online) online\n",
    ",sum(offline) offline\n",
    ",sum(ca) ca\n",
    ",sum(adx) adx\n",
    ",sum(sdk) sdk\n",
    ",sum(san_aff) san_aff\n",
    ",sum(san_pub) san_pub\n",
    "from t_cost_online\n",
    "group by dt\n",
    ")t1\n",
    "left join\n",
    "(\n",
    "select dt,pu_type,pu,online,offline,ca,adx,sdk,san_aff,san_pub from t_pu\n",
    ")t2 on t1.dt=t2.dt\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "fb24d5ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "#win\n",
    "#outpath=r'D:\\develop\\var\\data\\cloudcost\\out'\n",
    "#mac\n",
    "outpath='/Users/msxr/develop/var/cloudcost/out'\n",
    "\n",
    "cost_online_df.write.mode('overwrite').csv(outpath,header=True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "295626ba",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sample data of cost\n",
      "+----------+------------+------------+---------------+\n",
      "|        dt|service_type|service_name|           Cost|\n",
      "+----------+------------+------------+---------------+\n",
      "|2022-09-29|    基础服务|         CDN|3728.7909919373|\n",
      "|2022-09-30|    基础服务|         CDN|3460.8490752926|\n",
      "+----------+------------+------------+---------------+\n",
      "only showing top 2 rows\n",
      "\n",
      "sample data of online\n",
      "+----------+------------+------------+--------+-------+---+---+---+-------+--------+\n",
      "|        dt|service_type|service_name|  online|offline| ca|adx|sdk|san_aff| san_pub|\n",
      "+----------+------------+------------+--------+-------+---+---+---+-------+--------+\n",
      "|2022-09-26|    基础服务|         CDN|0.994538|    0.0|0.0|0.0|0.0|    0.0|0.005462|\n",
      "|2022-09-18|    基础服务|         CDN|0.994538|    0.0|0.0|0.0|0.0|    0.0|0.005462|\n",
      "+----------+------------+------------+--------+-------+---+---+---+-------+--------+\n",
      "only showing top 2 rows\n",
      "\n",
      "sample data of pu\n",
      "+----------+-------+---------+------+-------+---+---+---+-------+-------+\n",
      "|        dt|pu_type|       pu|online|offline| ca|adx|sdk|san_aff|san_pub|\n",
      "+----------+-------+---------+------+-------+---+---+---+-------+-------+\n",
      "|2022-09-08|    SAN|Affiliate|   0.0|    0.0|0.0|0.0|0.0|    1.0|    0.0|\n",
      "|2022-09-26|    SAN|Affiliate|   0.0|    0.0|0.0|0.0|0.0|    1.0|    0.0|\n",
      "+----------+-------+---------+------+-------+---+---+---+-------+-------+\n",
      "only showing top 2 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print('sample data of {}'.format('cost'))\n",
    "spark.read.option(\"header\", True).csv(cost_path).show(2)\n",
    "print('sample data of {}'.format('online'))\n",
    "spark.read.option(\"header\", True).csv(online_path).show(2)\n",
    "print('sample data of {}'.format('pu'))\n",
    "spark.read.option(\"header\", True).csv(pu_path).show(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d1f09a4",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "python",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "vscode": {
   "interpreter": {
    "hash": "40d3a090f54c6569ab1632332b64b2c03c39dcf918b08424e98f38b5ae0af88f"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
